package reporting;

import java.io.File;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import jxl.*;
import jxl.biff.DisplayFormat;
import jxl.format.CellFormat;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import util.*;
import static util.ConstantCollection.*;


public class ExcelReport extends TemplateReport {
	private FileWork fw = FileWork.getInstance();
	private TestConfiguration tc = TestConfiguration.getInstance();
	private String pathToLog;
	
	private Integer rowNumOfCountStep = 0;
	private Integer rowNumOfAutomatedStep = 0;
	private Integer rowNumOfPass = 0;
	private Integer rowNumOfFail = 0;
	private Integer rowNumOfDifficultImpl = 0;
	private Integer rowNumOfFistData = 0;
	private Integer rowNumOfLastData = 0;

	public ExcelReport(String name) {
		super(name);
				
		String tempPath;
		if(!tc.getEnv("pathtologs").contentEquals("[NO FOUND]") && fw.existsFileOrDir(tc.getEnv("pathtologs"))) {
			tempPath = tc.getEnv("pathtologs") + "log/" + tc.getEnv("release");
		} else {		
			tempPath ="log/" + tc.getEnv("release");
		}
		
		pathToLog = tempPath;
		
	    if(!fw.existsFileOrDir(pathToLog)){
	    	fw.createDir(pathToLog);
		}
	
	    pathToLog = pathToLog + "/summary.xls";

	    //If file is not exist, then create new
	    if(!fw.existsFileOrDir(pathToLog)){
	    	createBlankXlsFile(pathToLog);
	    } else {
	    	//do backup
    		DateFormat dateFormat = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
            Date date = new Date();
            String datetime = dateFormat.format(date);
            
    		WritableWorkbook backupWorkbook = copyWorkbook(pathToLog, tempPath + "/summary_bac_" + datetime + ".xls");
    		saveWorkbook(backupWorkbook);
	    }
    	    
	    //update file
	    WritableWorkbook workbook = copyWorkbook(pathToLog, pathToLog);
	    	        
	    //Check if there is sheet already
	    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
	    	if (workbook.getSheet(i).getName().contentEquals(tc.getEnv("_testname"))) {
	    		workbook.removeSheet(i); //delete sheet in main Excel file
	    	}
	    }
	    
	    //create new sheet and fill header
	    workbook.createSheet(tc.getEnv("_testname"), workbook.getNumberOfSheets());
	    WritableSheet wrSheet = workbook.getSheet(tc.getEnv("_testname"));
	    
	    wrSheet.setColumnView(0, 30);
	    putBoldText(wrSheet, 0, wrSheet.getRows(),  tc.getEnv("_testname"), 12);
	    putBoldText(wrSheet, 0, wrSheet.getRows() + 1,  "Total:", 9);
	    
	    rowNumOfCountStep = wrSheet.getRows();
	    putSimpleText(wrSheet, 0, rowNumOfCountStep,  "Count Step =", 10);
	    
	    rowNumOfAutomatedStep = wrSheet.getRows();
	    putSimpleText(wrSheet, 0, rowNumOfAutomatedStep,  "Automated Step =", 10);
	    
	    wrSheet.setColumnView(3, 15);
	    putSimpleText(wrSheet, 3, rowNumOfAutomatedStep,  "From of all Count =", 10);
	    
	    rowNumOfPass = wrSheet.getRows() + 1;
	    putSimpleText(wrSheet, 0, rowNumOfPass,  "Pass =", 10);
	    
	    rowNumOfFail = wrSheet.getRows();
	    putSimpleText(wrSheet, 0, rowNumOfFail,  "Fail =", 10);
	    
	    rowNumOfDifficultImpl = wrSheet.getRows();
	    putSimpleText(wrSheet, 0, rowNumOfDifficultImpl,  "Difficult Implement =", 10);
	    
	    rowNumOfFistData = wrSheet.getRows() + 1;
	    rowNumOfLastData = wrSheet.getRows() + 1;
	    
	    //put formulas to 'Summary'
	    WritableSheet wrSheetSummary = workbook.getSheet("Summary");
	    
	    putSimpleText(wrSheetSummary, 1, rowNumOfCountStep, "???", 10);
	    Integer numBuff1 = rowNumOfAutomatedStep + 1;
	    putFormulas(wrSheetSummary, 1, rowNumOfAutomatedStep, generateFormulasForSummary(workbook, "B" + numBuff1.toString()));//Automated Step =
	    Integer numBuff2 = rowNumOfCountStep + 1;
	    putFormulas(wrSheetSummary, 2, rowNumOfAutomatedStep, "B" + numBuff1.toString() + "/B" + numBuff2.toString());//Automated Step =
	    setCellFormat(wrSheetSummary, 2, rowNumOfAutomatedStep, NumberFormats.PERCENT_INTEGER);
	    
	    numBuff1 = rowNumOfPass + 1;
	    putFormulas(wrSheetSummary, 1, rowNumOfPass, generateFormulasForSummary(workbook, "B" + numBuff1.toString()));//Pass =
	    numBuff1 = rowNumOfFail + 1;
	    putFormulas(wrSheetSummary, 1, rowNumOfFail, generateFormulasForSummary(workbook, "B" + numBuff1.toString()));//Fail =
	    numBuff1 = rowNumOfDifficultImpl + 1;
	    putFormulas(wrSheetSummary, 1, rowNumOfDifficultImpl, generateFormulasForSummary(workbook, "B" + numBuff1.toString()));//Difficult Implement =
	    
		saveWorkbook(workbook);
		
	}
	
	private WritableWorkbook createBlankXlsFile(String fullPath) {
		WritableWorkbook wb = null;
        try {
        	WritableWorkbook workbook;
    		WorkbookSettings ws = new WorkbookSettings();
    	    ws.setLocale(new Locale("en", "EN"));
    	    workbook = Workbook.createWorkbook(new File(fullPath), ws);
    	    workbook.createSheet("Summary", 0);
    	    WritableSheet wrSheet = workbook.getSheet(0);
    	    
    	    wrSheet.setColumnView(0, 30);
    	    putBoldText(wrSheet, 0, wrSheet.getRows(),  "Summary(Release=" + tc.getEnv("release") + ")", 12);
    	    putBoldText(wrSheet, 0, wrSheet.getRows() + 1,  "Total:", 9);
    	    
    	    putSimpleText(wrSheet, 0, wrSheet.getRows(),  "Count Step =", 10); 
    	    putSimpleText(wrSheet, 0, wrSheet.getRows(),  "Automated Step =", 10);	    
    	    putSimpleText(wrSheet, 0, wrSheet.getRows() + 1,  "Pass =", 10);
    	    putSimpleText(wrSheet, 0, wrSheet.getRows(),  "Fail =", 10);
    	    putSimpleText(wrSheet, 0, wrSheet.getRows(),  "Difficult Implement =", 10);
    	    
    	    workbook.write();
    	    workbook.close();    
       } catch (Exception e) {
            System.out.println(e.getMessage());
       }
       
       return wb;
	}
	
	private WritableWorkbook copyWorkbook(String inputFullPath, String outputFullPath) {
		 //read excel file
	    Workbook wb = null;
	    try {
	    	wb = Workbook.getWorkbook(new File(inputFullPath));
	    } catch (Exception e) {
	    	System.out.println(e.getMessage());
	    }
	    
	    //copy excel file
		WorkbookSettings ws = new WorkbookSettings();
	    ws.setLocale(new Locale("en", "EN"));
	    WritableWorkbook workbook = null;
	    
	    try {
	    	workbook = Workbook.createWorkbook(new File(outputFullPath), wb, ws);
	    } catch (Exception e) {
	    	System.out.println(e.getMessage()); 
	    }
	    
	    return workbook;
	}
	
	private void saveWorkbook(WritableWorkbook workbook) {
		try {
	    	workbook.write();
	    	workbook.close();
	    } catch (Exception e) {
	    	System.out.println(e.getMessage());
	    }
	}

	private void putBoldText(WritableSheet wrSheet, Integer cellNum, Integer rowNum, String text, Integer fontSize) {
		WritableFont timesNpt = new WritableFont(WritableFont.TIMES, fontSize, WritableFont.BOLD);
	    WritableCellFormat timesNformat = new WritableCellFormat(timesNpt);
	    Label lr = new Label(cellNum, rowNum, text, timesNformat);
	    try {
	    	wrSheet.addCell(lr);
	    } catch (Exception e) {
	    	System.out.println(e.getMessage());
	    }
	}
	
	private void putSimpleText(WritableSheet wrSheet, Integer cellNum, Integer rowNum, String text, Integer fontSize) {
		WritableFont timesNpt = new WritableFont(WritableFont.TIMES, fontSize);
	    WritableCellFormat timesNformat = new WritableCellFormat(timesNpt);
	    Label lr = new Label(cellNum, rowNum, text, timesNformat);
	    try {
	    	wrSheet.addCell(lr);
	    } catch (Exception e) {
	    	System.out.println(e.getMessage());
	    }
	}
	
	private void putFormulas(WritableSheet wrSheet, Integer cellNum, Integer rowNum, String formulas) {
		Formula f = new Formula(cellNum, rowNum, formulas);
	    try {
	    	wrSheet.addCell(f);
	    } catch (Exception e) {
	    	System.out.println(e.getMessage());
	    }
	}
	
/*	private void putNumberAsPercent(WritableSheet wrSheet, Integer cellNum, Integer rowNum, Integer number) {
		WritableCellFormat cf = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
	    Number n = new Number(cellNum, rowNum, number, cf);
	    try {
	    	wrSheet.addCell(n);
	    } catch (Exception e) {
	    	System.out.println(e.getMessage());
	    }
	} */
	
	private void setCellFormat(WritableSheet wrSheet, Integer cellNum, Integer rowNum, DisplayFormat cellFormat){
		CellFormat cf = new WritableCellFormat(cellFormat);
     	WritableCell wrCell = wrSheet.getWritableCell(cellNum, rowNum);
     	wrCell.setCellFormat(cf);
	}
	
	private String generateFormulasForSummary(WritableWorkbook workbook, String cell) {
		String result = "";
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
	    	if (!workbook.getSheet(i).getName().contentEquals("Summary")) {
	    		if (!result.contentEquals("")) {
	    			 result = result + "+" ;
	    		}
	    		
	    		result = result + workbook.getSheet(i).getName() + "!" + cell;
	    	}
	    }
		
		return result;
		
	}
	
	public void reportStep(TCStep step){
		WritableWorkbook workbook = copyWorkbook(pathToLog, pathToLog);
		WritableSheet wrSheet = workbook.getSheet(tc.getEnv("_testname"));
		
		if (!step.getId().contentEquals("***") && !step.getId().contentEquals("000") && step.getStatus() > 2) {
			putSimpleText(wrSheet, 0, rowNumOfLastData,  step.getId(), 10);
			putSimpleText(wrSheet, 1, rowNumOfLastData,  getStatusName(step.getStatus()), 10);
		}
      
        //update all formulas
        Integer numBuff1 = rowNumOfFistData+1;
        Integer numBuff2 = rowNumOfLastData+1;
        putFormulas(wrSheet, 1, rowNumOfCountStep, "ROWS(A" + numBuff1.toString() + ":A" + numBuff2.toString() + ")"); //Count Step
        putFormulas(wrSheet, 1, rowNumOfPass, "COUNTIF(B" + numBuff1.toString() + ":B" + numBuff2.toString() + ",\"PASS\")");//Pass =
        putFormulas(wrSheet, 1, rowNumOfFail, "COUNTIF(B" + numBuff1.toString() + ":B" + numBuff2.toString() + ",\"FAIL\") + COUNTIF(B" + numBuff1.toString() + ":B" + numBuff2.toString() + ",\"ERROR\")");//Fail =
        putFormulas(wrSheet, 1, rowNumOfDifficultImpl, "COUNTIF(B" + numBuff1.toString() + ":B" + numBuff2.toString() + ",\"WARNING\")");//Difficult Implement =
        
        numBuff1 = rowNumOfCountStep+1;
        numBuff2 = rowNumOfDifficultImpl+1;
     	putFormulas(wrSheet, 1, rowNumOfAutomatedStep, "B" + numBuff1.toString() + "-B" + numBuff2.toString()); //Automated Step =
     	numBuff2 = rowNumOfAutomatedStep+1;
     	
     	putFormulas(wrSheet, 2, rowNumOfAutomatedStep, "B" + numBuff2.toString() + "/B" + numBuff2.toString()); //Automated Step =
     	setCellFormat(wrSheet, 2, rowNumOfAutomatedStep, NumberFormats.PERCENT_INTEGER);
     	
     	putFormulas(wrSheet, 4, rowNumOfAutomatedStep, "B" + numBuff2.toString() + "/Summary!B" + numBuff1.toString()); //Automated Step =
     	setCellFormat(wrSheet, 4, rowNumOfAutomatedStep, NumberFormats.PERCENT_INTEGER);
		
     	rowNumOfLastData = wrSheet.getRows();
		saveWorkbook(workbook);
	}
}
